﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data;
using SqlMiM.SqlServer.Model;
using SqlMiM.Common;
using SqlMiM.Common.Trace;
using SqlMiM.Common.Model;

namespace SqlMiM.SqlServer.Core
{
    public class SpaceManagementServices
    {

        #region Singleton

        private static SpaceManagementServices current;

        public static SpaceManagementServices Current
        {
            get
            {
                if (current == null)
                {
                    current = new SpaceManagementServices();
                }

                return current;
            }
        }

        #endregion

        private SqlCommand selectFileUsagesCommand;
        private SqlCommand selectFileGroupsCommand;
        private SqlCommand selectDataFilesCommand;

        public SpaceManagementServices()
        {
        }


        public List<FilesStat> GetFilesStatistics()
        {
            List<FilesStat> filesStats = null;
            SqlConnection connection = Context.Current.GetConnection();
            SqlDataReader reader = null;

            try
            {
                this.SelectFileUsagesCommand.Connection = connection;

                connection.Open();

                reader = this.SelectFileUsagesCommand.ExecuteReader();

                if (reader.HasRows)
                {
                    filesStats = new List<FilesStat>();

                    while (reader.Read())
                    {
                        FilesStat fs = new FilesStat();
                        fs.DatabaseName = reader["DatabaseName"] as String;
                        fs.DatabaseId = (Int32)reader["DatabaseId"];
                        fs.Reads = reader["Reads"] != System.DBNull.Value ? (Int64)reader["Reads"] : (Int64)0;
                        fs.ReadsMo = reader["ReadsMo"] != System.DBNull.Value ? (double)reader["ReadsMo"] : 0d;
                        fs.Writes = reader["Writes"] != System.DBNull.Value ? (Int64)reader["Writes"] : (Int64)0;
                        fs.WritesMo = reader["WritesMo"] != System.DBNull.Value ? (double)reader["WritesMo"] : 0d;
                        fs.TotalIo = reader["TotalIo"] != System.DBNull.Value ? (Int64)reader["TotalIo"] : (Int64)0;
                        fs.TotalIoMo = reader["TotalIoMo"] != System.DBNull.Value ? (double)reader["TotalIoMo"] : 0d;

                        filesStats.Add(fs);
                    }
                }
                reader.Close();

                connection.Close();
            }
            catch (SqlException sqlEx)
            {
                Notifier.Current.Notify(sqlEx);
            }
            catch (Exception ex)
            {
                Notifier.Current.Notify(ex);
            }
            finally
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }


            return filesStats;

        }



        public List<FileGroup> GetFileGroupsStatistics(Database db)
        {
            List<FileGroup> fileGroupsStats = null;
            SqlConnection connection = Context.Current.GetConnection(db.Name);
            SqlDataReader reader = null;

            try
            {
                this.SelectFileGroupsCommand.Connection = connection;

                connection.Open();

                reader = this.SelectFileGroupsCommand.ExecuteReader();

                if (reader.HasRows)
                {
                    fileGroupsStats = new List<FileGroup>();

                    while (reader.Read())
                    {
                        FileGroup fgv = new FileGroup();

                        fgv.DatabaseName = db.Name;
                        fgv.FileGroupName = reader["FgName"] as String;
                        fgv.FilesCount = reader["NbFiles"] != System.DBNull.Value ? (int)reader["NbFiles"] : 0;
                        fgv.CurrentSize = reader["CurrentSizeMB"] != System.DBNull.Value ? (Double)reader["CurrentSizeMB"] : 0;
                        fgv.FreeSpace = reader["FreeSpaceMB"] != System.DBNull.Value ? (Double)reader["FreeSpaceMB"] : 0;
                        fgv.SpaceUsed = reader["SpaceUsed"] != System.DBNull.Value ? (Double)reader["SpaceUsed"] : 0;

                        if (fgv.CurrentSize > 0)
                            fileGroupsStats.Add(fgv);
                    }
                }
                reader.Close();

                connection.Close();

            }
            catch (SqlException sqlEx)
            {
                Notifier.Current.Notify(sqlEx);
            }
            catch (Exception ex)
            {
                Notifier.Current.Notify(ex);
            }
            finally
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                if (reader != null)
                {
                    reader.Dispose();
                    reader = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }

            return fileGroupsStats;

        }

        internal SqlCommand SelectFileUsagesCommand
        {
            get
            {
                if (selectFileUsagesCommand == null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("    Declare @OneMo float; Set @OneMo = cast ((1024 * 1024) as float); ");
                    sb.AppendLine("    SELECT  vfs.sample_ms  as EllapsedTimeMs ");
                    sb.AppendLine("    , db_name(vfs.database_id) as DatabaseName  ");
                    sb.AppendLine("    , Cast(vfs.database_id as int) as DatabaseId  ");
                    sb.AppendLine("    , sum(vfs.num_of_bytes_read / @OneMo) as [ReadsMo] ");
                    sb.AppendLine("    , sum(vfs.num_of_reads) as [Reads] ");
                    sb.AppendLine("    , sum(vfs.num_of_bytes_written / @OneMo) as [WritesMo] ");
                    sb.AppendLine("    , sum(vfs.num_of_writes) as [Writes] ");
                    sb.AppendLine("    , sum(vfs.num_of_bytes_read + vfs.num_of_bytes_written) / @OneMo as [TotalIoMo] ");
                    sb.AppendLine("    , sum(vfs.num_of_writes + vfs.num_of_reads) as [TotalIo] ");
                    sb.AppendLine("    From sys.dm_io_virtual_file_stats(null, null) vfs ");
                    sb.AppendLine("    Where vfs.file_id <> (Select mf.file_id from sys.master_files mf where mf.type = 1 and mf.database_id = vfs.database_id) ");
                    sb.AppendLine("    And vfs.database_id > 4 ");
                    sb.AppendLine("    Group by vfs.sample_ms, vfs.database_id ");
                    sb.AppendLine("    Order By DatabaseName asc");

                    selectFileUsagesCommand = new SqlCommand();
                    selectFileUsagesCommand.CommandType = CommandType.Text;
                    selectFileUsagesCommand.CommandText = sb.ToString();

                }

                return selectFileUsagesCommand;
            }
        }

        internal SqlCommand SelectFileGroupsCommand
        {
            get
            {
                if (selectFileGroupsCommand == null)
                {
                    StringBuilder sb = new StringBuilder();


                    sb.AppendLine("    SELECT fg.name AS FgName, ");
                    sb.AppendLine("    count(f.name) AS NbFiles,  ");
                    sb.AppendLine("    sum(f.size/cast(128 as float)) AS CurrentSizeMB, ");
                    sb.AppendLine("    sum(CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS float)/cast(128 as float)) as SpaceUsed, ");
                    sb.AppendLine("    sum(f.size/cast(128 as float)-CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS float)/cast(128 as float)) AS FreeSpaceMB ");
                    sb.AppendLine("    FROM sys.database_files f ");
                    sb.AppendLine("    inner join sys.filegroups fg on fg.data_space_id = f.data_space_id ");
                    sb.AppendLine("    group by fg.name ");

                    selectFileGroupsCommand = new SqlCommand();
                    selectFileGroupsCommand.CommandType = CommandType.Text;
                    selectFileGroupsCommand.CommandText = sb.ToString();

                }

                return selectFileGroupsCommand;
            }
        }

        internal SqlCommand SelectDataFilesCommand
        {
            get
            {
                if (selectDataFilesCommand == null)
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("    Select ");
                    sb.AppendLine("    CAST(CASE f.is_percent_growth WHEN 1 THEN f.growth ELSE f.growth / cast(128 as float) END AS float) AS growth, ");
                    sb.AppendLine("    CAST(CASE when f.growth=0 THEN (CASE WHEN f.type = 2 THEN 0 ELSE 99 END) ELSE f.is_percent_growth END AS int) AS growthType, ");
                    sb.AppendLine("    f.name,  ");
                    sb.AppendLine("    f.type,  ");
                    sb.AppendLine("    CAST(CASE when f.max_size=-1 then -1 else f.max_size / cast(128 as float) END as float ) AS maxSizeMB, ");
                    sb.AppendLine("    f.size / cast(128 as float) as sizeMB, ");
                    sb.AppendLine("    f.size/ cast(128 as float) -CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS float)/ cast(128 as float) AS freeSpaceMB , ");
                    sb.AppendLine("    CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS float) / cast(128 as float) as spaceUsed ");
                    sb.AppendLine("    from sys.database_files AS f ");
                    sb.AppendLine("    inner join sys.filegroups fg on fg.data_space_id = f.data_space_id ");
                    sb.AppendLine("    where fg.name = @fgName");
                    sb.AppendLine("    order by f.type, f.name ");

                    selectDataFilesCommand = new SqlCommand();
                    selectDataFilesCommand.CommandType = CommandType.Text;
                    selectDataFilesCommand.CommandText = sb.ToString();

                    var fgParam = new SqlParameter();
                    fgParam.SqlDbType = SqlDbType.VarChar;
                    fgParam.ParameterName = "@fgName";

                    selectDataFilesCommand.Parameters.Add(fgParam);

                }

                return selectDataFilesCommand;
            }
        }

        //foreach (var dbView in databasesView)
        //{
        //    sqlConnectionStringBuilder.InitialCatalog = dbView.DatabaseName;
        //    this.connection = new SqlConnection(sqlConnectionStringBuilder.ToString());

        //    this.SelectDataFilesCommand.Connection = this.connection;
        //    this.connection.Open();

        //    foreach (var fileGroup in dbView.FileGroupsView)
        //    {
        //        this.SelectDataFilesCommand.Parameters[0].Value = fileGroup.FileGroupName;
        //        var readerFiles = this.SelectDataFilesCommand.ExecuteReader();

        //        while (readerFiles.Read())
        //        {
        //            DataFileView dfv = new DataFileView();
        //            dfv.FileName = readerFiles["name"] as String;
        //            dfv.Growth = readerFiles["growth"] != System.DBNull.Value ? (double)readerFiles["growth"] : 0d;
        //            dfv.MaxSize = readerFiles["maxSizeMB"] != System.DBNull.Value ? (double)readerFiles["maxSizeMB"] : 0d;
        //            dfv.Size = readerFiles["sizeMB"] != System.DBNull.Value ? (double)readerFiles["sizeMB"] : 0d;
        //            dfv.UsedSpace = readerFiles["spaceUsed"] != System.DBNull.Value ? (double)readerFiles["spaceUsed"] : 0d;
        //            dfv.FreeSpace = readerFiles["freeSpaceMB"] != System.DBNull.Value ? (double)readerFiles["freeSpaceMB"] : 0d;
        //            dfv.GrowthType = readerFiles["growthType"] != System.DBNull.Value ? (FileGrowthType)readerFiles["growthType"] : FileGrowthType.None;
        //            dfv.Type = readerFiles["type"] != System.DBNull.Value ? (DataFileView.FileType)readerFiles["type"] : DataFileView.FileType.Rows;

        //            fileGroup.DataFilesView.Add(dfv);

        //        }
        //        readerFiles.Close();
        //        readerFiles.Dispose();
        //        readerFiles = null;

        //    }

        //    this.connection.Close();

        //}
    }
}
